﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace CreatePGSQLDataScript
{
    public partial class CreateScript : Form
    {
        public CreateScript()
        {
            InitializeComponent();
        }
        //DataAccess.PgSqlProvider db;
        private void btnConn_Click(object sender, EventArgs e)
        {
            string connString = cmbConn.Text.Trim();

            try
            {
                //db = new DataAccess.PgSqlProvider(connString);
                getDataTable(connString, "select 1;");
                historyList.SaveValue(cmbConn);
                MessageBox.Show("連接成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }


        }

        private void btnExecute_Click(object sender, EventArgs e)
        {
            try
            {
                string connString = cmbConn.Text.Trim();
                //db = new DataAccess.PgSqlProvider(connString);

                DataTable dt = getDataTable(connString, rtxtSql.Text);
                string TableName = new Regex(@"from[\s]*([\S]*)").Match(rtxtSql.Text).Groups[1].Value;
                dt.TableName = TableName;

                string script = "";

                if (rdoAdd.Checked)
                {
                    script = getInsertScript(dt);
                }
                else if (rdoUpd.Checked)
                {
                    script = getUpdateScript(dt);
                }
                else if (rdoDel.Checked)
                {
                    script = getDeleteScript(dt);
                }



                rtxtScript.Text = script.ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private string getInsertScript(DataTable dt)
        {
            string fields = "";
            StringBuilder tableScript = new StringBuilder();
            foreach (DataColumn dc in dt.Columns)
            {
                fields += "," + dc.ColumnName;
            }
            fields = fields.Substring(1);


            foreach (DataRow dr in dt.Rows)
            {
                string rowValue = "";
                string rowScript = "";
                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.AutoIncrement)
                        continue;

                    if (dr[dc] == DBNull.Value)
                    {
                        rowValue += ",null";
                    }
                    else
                    {
                        string value = dr[dc].ToString();
                        value = ProcessSpecialLetter(value);
                        rowValue += ",'" + value + "'";
                        //if (dc.DataType.FullName != "System.String" && string.IsNullOrEmpty(value))
                        //{
                        //    rowValue += ",null";
                        //}
                        //else
                        //{
                        //    rowValue += ",'" + value + "'";
                        //}
                    }


                }
                rowValue = rowValue.Substring(1);
                rowScript = "insert into " + dt.TableName + "(" + fields + ") values(" + rowValue + ");";

                tableScript.AppendLine(rowScript);

            }
            return tableScript.ToString();
        }

        private string getDeleteScript(DataTable dt)
        {
            List<string> Keys= getKey(dt);
            if (Keys == null)
            {
                return "表無主鍵！";
            }
                
            StringBuilder sbScript = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                string condition = getCondition(Keys, dr);
                string rowScript = string.Format("delete from {0} {1};\n", dt.TableName, condition);
                sbScript.Append(rowScript);
            }
            return sbScript.ToString();
        }

        private string getUpdateScript(DataTable dt)
        {
            List<string> Keys = getKey(dt);
            if (Keys == null)
            {
                return "表無主鍵！";
            }

            StringBuilder sbScript = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                string condition = getCondition(Keys, dr);

                string keyValules = "";
                foreach (DataColumn dc in dt.Columns)
                {
                    if (Keys.Contains(dc.ColumnName))
                        continue;
                    string value = "";
                    if (dc.DataType.FullName != "System.String" && string.IsNullOrEmpty(dr[dc].ToString()))
                    {
                        value = "null";
                    }
                    else
                    {
                        value = ProcessSpecialLetter(value);
                        value = "'" + dr[dc].ToString() + "'";
                        
                    }

                    keyValules += "," + dc.ColumnName + "=" + value;

                }
                keyValules = keyValules.Substring(1);
                string rowScript = string.Format("update {0} set {1} {2};\n", dt.TableName, keyValules, condition);
                sbScript.Append(rowScript);
            }
            return sbScript.ToString();
        }

        private string getCondition(List<string> Keys, DataRow dr)
        {
            string condition = "";
            foreach (string key in Keys)
            {
                condition += string.Format("and {0}='{1}'", key, dr[key].ToString());
            }

            if (condition != "")
            {
                condition = "where "+ condition.Substring(4);
            }

            return condition;
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            historyList.ReadList(cmbConn);
            //txtConnString.Text = "server=192.168.1.71;database=justgogocenter;uid=postgres;pwd=`12345;MaxPoolSize=500;";
        }

//        private List<string> getKey(string tableName)
//        {
//            List<string> Keys =new List<string>();
//            string sqlText=string.Format(@"
//                                select conkey from pg_constraint as c 
//                                 inner join pg_class as o on c.conrelid=o.oid  where relname='{0}';",tableName);


//            short[] keyNum = (short[])db.ExecuteScalar(sqlText);
//            if (keyNum.Length <= 0)
//                return null;
//            string keyNums = "";
//            foreach (short num in keyNum)
//            {
//                keyNums += "," + num.ToString();
//            }
//            keyNums = keyNums.Substring(1);

            
//            sqlText =string.Format(@"
//                        select attname from pg_attribute as a
//                        inner join pg_class as o on a.attrelid=o.oid 
//                        where relname='{0}'
//                        and attnum in({1})  order by attnum; ", tableName,keyNums);

//            DataTable dt= db.GetDataTable(sqlText);
//            foreach(DataRow dr in dt.Rows)
//            {
//                Keys.Add(dr["attname"].ToString());
//            }
//            return Keys;

//        }

        private List<string> getKey(DataTable dt)
        {
            List<string> Keys =new List<string>();
            foreach (var dc in dt.PrimaryKey)
            {
                Keys.Add(dc.ColumnName);
            }
            return Keys;
        }

        private string ProcessSpecialLetter(string value)
        {
            value = value.Replace("'", "''");
            return value;
        }

        private DataTable getDataTable(string connection, string sqlText)
        {
            DataTable dt = new DataTable();
            if (rdoPgSql.Checked)
            {
                DataAccess.PgSqlProvider db = new DataAccess.PgSqlProvider(connection);
                return db.GetDataTable(sqlText);
            }
            if (rdoSqlServer.Checked)
            {
                DataAccess.SqlProvider db = new DataAccess.SqlProvider(connection);
                return db.GetDataTable(sqlText);
            }
            return null;
        }


    }
}
